define(`_agr_ID',`ID INTEGER PRIMARY KEY')
define(`_agr_Created',`Created DATETIME DEFAULT CURRENT_TIMESTAMP')
define(`_agr_Modified',`Modified DATETIME DEFAULT CURRENT_TIMESTAMP')

define(`_agr_Mod_trigger',
`CREATE TRIGGER $1_Modified AFTER UPDATE ON $1
BEGIN
UPDATE $1 SET Modified = datetime("now") WHERE ID = NEW.ID;
END')

define(`_agr_ColonyID',`ColonyID INTEGER NOT NULL REFERENCES Colony(ID)')

_db_create(Colony_table,
`CREATE TABLE Colony (
	_agr_ID(),
	_agr_Created(),
	_agr_Modified(),
	Name TEXT UNIQUE NOT NULL,
    GenderDefault TEXT NOT NULL,
    CHECK(case GenderDefault when "M" then 1 when "F" then 1 when "N" then 1 else 0 end)); ')

_db_create(Colony_Mod_trigger, 
_agr_Mod_trigger(Colony);
)

_db_create(Genotype_table, `
CREATE TABLE Genotype ( 
	_agr_ID(),
	_agr_ColonyID(),
	_agr_Created(),
	_agr_Modified(),
	Name VARCHAR NOT NULL, 
	UNIQUE(ColonyID, Name) 
);
')

_db_create(Genotype_Mod_trigger,
_agr_Mod_trigger(Genotype);
)

_db_create(Mouse_table, `
CREATE TABLE Mouse (
	_agr_ID(),
	_agr_ColonyID(),
	_agr_Created(),
	_agr_Modified(),
	DateOfBirth DATE NOT NULL DEFAULT CURRENT_DATE,
	Disabled BOOLEAN DEFAULT 0,
	Gender VARCHAR,
	GenotypeID INTEGER REFERENCES Genotype(ID),
	Tag VARCHAR
);
')

_db_create(Mouse_Mod_trigger, 
_agr_Mod_trigger(Mouse);
)

_db_create(Experiment_table, `
CREATE TABLE Experiment (
	_agr_ID(),
	_agr_ColonyID(),
	_agr_Created(),
	_agr_Modified(),
	Name TEXT NOT NULL,
	Priority INTEGER,
	UNIQUE(ColonyID, Name)
);
')

_db_create(Experiment_Mod_trigger, _agr_Mod_trigger(Experiment); )

_db_create(ExperimentControlGroup_table, `
CREATE TABLE ExperimentControlGroup (
	_agr_ID(),
	ExperimentID INTEGER NOT NULL REFERENCES Experiment(ID),
	_agr_Created(),
	_agr_Modified(),
	Name TEXT NOT NULL,
	GenotypeID INTEGER REFERENCES Genotype(ID),
	MouseQuantity INTEGER NOT NULL DEFAULT 1,
	AgeDaysUpperLimit INTEGER,
	AgeDaysLowerLimit INTEGER,
	UNIQUE(ExperimentID,Name)
);
')

_db_create(ExperimentControlGroup_Mod_trigger, _agr_Mod_trigger(ExperimentControlGroup); )

_db_create(ExperimentCandidates_table, `
CREATE TABLE ExperimentCandidates (
	_agr_ID(),
	ExperimentControlGroupID INTEGER NOT NULL REFERENCES ExperimentControlGroup(ID),
	_agr_Created(),
	_agr_Modified(),
	MouseID INTEGER NOT NULL REFERENCES Mouse(ID)
);
')

_db_create(ExperimentCandidates_Mod_trigger, _agr_Mod_trigger(ExperimentCandidates); )

_db_create(ExperimentAssignment_table, `
CREATE TABLE ExperimentAssignment (
	_agr_ID(),
	ExperimentControlGroupID INTEGER NOT NULL REFERENCES ExperimentControlGroup(ID),
	_agr_Created(),
	_agr_Modified(),
	MouseID INTEGER NOT NULL REFERENCES Mouse(ID),
	CheckDate DATETIME NOT NULL
);
')

_db_create(ExperimentAssignment_Mod_trigger, _agr_Mod_trigger(ExperimentAssignment); )

_db_create(AppState_table, `
CREATE TABLE AppState (
	_agr_ID(),
	_agr_Created(),
	URL VARCHAR NOT NULL
);
')

_db_create(Colony_init, `
insert or rollback into
	Colony (Name,GenderDefault) 
values
	("EPO","N");
')

_db_create(Genotype_init, `
insert or rollback into
	Genotype (ColonyID, Name)
select Colony.ID, "Epo fl/fl" from Colony where Name = "EPO"
union all
select Colony.ID, "C57BL" from Colony where Name = "EPO"
;
')

_db_create(Mouse_init, `
insert or rollback into
	Mouse (ColonyID, Tag)
select Colony.ID, "1A" from Colony where Name = "Test Colony"
union all
select Colony.ID, "1B" from Colony where Name = "Test Colony"
;
')
